<?php if( ! defined('BASEPATH')) exit('No direct script access allowed');
/* ************************************************** *\
 * @company		: NovaITech							  *	
 * @author		: van.vo@novaitech.com				  * 
 * @copyright	: 2012								  * 
 * ================================================== *
 * @date		: 04/26/2012
 * @project		: Drag & Drop
 * @description	: 
 *		- 
 *
\* ************************************************** */

class Tongket extends MY_Controller{
    function __construct( ){
        parent::__construct( );  
		$this->load->helper( array( 'form', 'url' ) );
		$this->load->model( 'CodeGenerator_model', '', TRUE );
		$this->load->helper('date');
		$this->load_template();
    }
	
	function index(){
        $role = $this->session->userdata('role');
		$time_from = $this->input->get('time_from');
		if(empty($time_from))
			$time_from=$this->input->post('thongke_dt_from');
		$time_to = $this->input->get('time_to');
		if(empty($time_to))
			$time_to=$this->input->post('thongke_dt_to');
		
		if(empty($time_from))
			$time_from = date("d/m/Y",time());
		if(empty($time_to))
			$time_to = date("d/m/Y",time());
		
		$this->data['time_from'] = $time_from;
		$this->data['time_to'] = $time_to;
		
		$this->data['permit'] = $this->checkpermission();
		//do tong ket thu	
		
		$limit['datemin']= $this->convertDatetoTimeStamp($time_from,1);
		$limit['datemax']= $this->convertDatetoTimeStamp($time_to,0);
		
		$rules                  =         array(array('field_id' => 'muc','label' => 'Muc','control' => 'string','class'=>'first border-right','width'=>'10%','style'=>'border-right: solid 3px #fff;'),array('field_id' => 'dichvu','label' => 'Dịch vụ','control' => 'string','class'=>'border-right','width'=>'30%','style'=>'border-right: solid 3px #fff;'),array('field_id' => 'kh','label' => '#KH','control' => 'auto','class'=>'border-right','width'=>'5%','style'=>'border-right: solid 3px #fff;'),array('field_id' => 'giatridichvu','label' => 'Gia Tri Dich Vu','control' => 'vnd','class'=>'border-right','width'=>'15%','style'=>'border-right: solid 3px #fff;'),array('field_id' => 'tips','label' => 'Tips','control' => 'vnd','class'=>'border-right','width'=>'10%','style'=>'border-right: solid 3px #fff;'),array('field_id' => 'percent_service','label' => '$<br/>Dich Vu','control' => 'vnd','class'=>'border-right','width'=>'15%','style'=>'border-right: solid 3px #fff;'),array('field_id' => 'conlai','label' => 'Con lai','control' => 'vnd','class'=>'last','width'=>'15%','style'=>''));
        $this->data[ 'rules' ]  =   $rules;
		
		 $header = array( );
        foreach( $rules as $item ){
            $header[ ] = $item[ 'label' ];
        }
       
        $this->data[ 'header' ] =   $header;
		
		//paging
        $config['base_url'] = base_url().'tongket/index/';
		$config['suffix']="";
		// if($filter) {
			// $config['suffix'] = "/?filter=$filter&orderby=$orderby&time=$time";
			// $config['first_url'] = $config['base_url']."0/?filter=$filter&orderby=$orderby&time=$time";
		// }
		if(empty($config['suffix'])){
			$config['suffix'] = "/?time_from=$time_from&time_to=$time_to";
			$config['first_url'] = $config['base_url']."0/?time_from=$time_from&time_to=$time_to";
		}
		$sql = "select count(Tran_ID) as total from transaction where Completed = 2 and Create_Dt >= ".$limit['datemin']." AND Create_Dt <= ".$limit['datemax'];
		$total = $this->CodeGenerator_model->custom_query($sql,"array");
		if(!empty($total)) $config['total_rows'] = $total[0]['total'];
		else $config['total_rows'] = 0;
        // $config['total_rows'] = $this->CodeGenerator_model->count('transaction');
        $config['per_page'] = PER_PAGE;	
        $this->pagination->initialize($config); 
		
		$start = $this->uri->segment(3);
		if(empty($start)) $start = 0;
		$perpage = $config['per_page'];
		
		//end
		//lay danh sach giao dich
		
		$sql = "select t.*, CONCAT(c.F_Name, ' ' , c.L_Name) as khachhang, p.SoSuat, p.Conlai,p.Tang from customer as c, transaction as t left join phieukh as p on t.Phieu_ID=p.ID where t.Completed = 2 and c.Cust_ID = t.Cust_ID AND t.Create_Dt >= ".$limit['datemin']." AND t.Create_Dt <= ".$limit['datemax']." order by t.Create_Dt Limit $start,$perpage";

        $results=$this->CodeGenerator_model->custom_query($sql,"array");
        // Make action Edit/Delete
        // --------------------------------------------------
		//lấy giá, giảm, thành tiền (chỉ lấy đc 3 field này thui)
		$sql = "select sum(Price) as gia, sum(Discount_Percent) as giam, sum(Money) as thanhtien, sum(Bonus) as bonus from transaction where Create_Dt >= ".$limit['datemin']." AND Create_Dt <= ".$limit['datemax'];
		$total = $this->CodeGenerator_model->custom_query($sql,"array");
		$gia = 0; $giam=0; $thanhtien = 0; 
		
		if(!empty($total)){
			$gia = $total[0]['gia'];
			$giam = $total[0]['giam'];
			$thanhtien = $total[0]['thanhtien'];
		}
		//lấy tiền típ
		$this->data['tientip']= $this->getTientip($limit['datemin'],$limit['datemax']);
		//end
		
		$this->data['gia'] = $gia;
		$this->data['giam'] = $giam;
		$this->data['thanhtien'] = $thanhtien;
		$this->data['perpage'] = $perpage;
		//foreach dich vụ
        for( $i = 0; $i < count( $results ); $i++ ){
	       $id = array_values( $results[ $i ]);
		   $Tran_ID = $results[$i]['Tran_ID'];
		   //duyet service va nguoi duoc tip
		   $str_dv = "";
		   $str_muc = "";
		   $str_nguoitip = 0;
		   //lấy ~ giao dịch ko fải là phiếu, và VIP
		   $sql = "select s.Serv_Name, ca.Name, e.Personal_ID,tt.Price,tt.Price_Serv  from services as s, categories as ca, employee as e, tientip as tt where tt.Tran_ID='$Tran_ID' and tt.Vip=0 AND s.Cat_ID = ca.Ca_ID and e.EE_ID = tt.EE_ID and tt.Serv_ID = s.Serv_ID";
		   // echo $sql;die;
		   $services = $this->CodeGenerator_model->custom_query($sql,"array");
		   // print_r($services);
			$tiendv = 0;
		   foreach($services as $t){
				$str_muc.=$t['Name']."<br/>";
				$str_dv .=$t['Serv_Name']."<br/>";
				$str_nguoitip += $t['Price'];
				$tiendv +=$t['Price_Serv'];
		   }  
		   
			//lấy ~ những giao dịch VIP
			$sql = "select tt.Serv_ID,tt.ID,s.Serv_Name,tt.Price, tt.Price_Serv, ca.Name from tientip as tt, services as s,categories as ca where s.Serv_ID = tt.Serv_ID and tt.Tran_ID='$Tran_ID' and tt.Vip=1 AND s.Cat_ID = ca.Ca_ID";
			$services_vip = $this->CodeGenerator_model->custom_query($sql,"array");
			foreach($services_vip as $t){
				$str_muc.=$t['Name']."<br/>";
				$str_dv .=$t['Serv_Name']."<br/>";
				$str_nguoitip += $t['Price'];
				$tiendv +=$t['Price_Serv'];
			}
		   
		   //lấy ~ giao dịch là phiếu
		   $sql = "select s.Serv_Name, ca.Name, e.Personal_ID, p.TienTip,p.SoSuat, p.Conlai,p.Price_Serv from services as s, categories as ca, employee as e, phieukh as p where p.Tran_ID='$Tran_ID' and p.EE_ID = e.EE_ID and s.Cat_ID = ca.Ca_ID and p.Serv_ID = s.Serv_ID and p.Active=1";
		   $phieus = $this->CodeGenerator_model->custom_query($sql,"array");
		   foreach($phieus as $t){
				$str_muc.=$t['Name']."<br/>";
				$str_dv .="Phieu ".$t['SoSuat']." suat - con lai ".$t['Conlai']." suat - ".$t['Serv_Name']."<br/>";
				$str_nguoitip += $t['TienTip'];
				$tiendv +=$t['Price_Serv'];
		   }
		   
		   $results[$i]['muc'] =  substr($str_muc,0,-5);
		   $results[$i]['dichvu'] = substr($str_dv,0,-5);
		   $results[$i]['kh'] = $i+1+ $start;
		   $results[$i]['giatridichvu'] = $results[$i]['Price'];
		   $results[$i]['percent_service'] = $tiendv;
		   $results[$i]['tips'] = $str_nguoitip;
		   $results[$i]['conlai'] = $results[$i]['Price'] - $results[$i]['tips'] - $results[$i]['percent_service'];
        }
		//end foreach dich vụ
		//kh cu
		$this->data['khcu'] = $this->getKHCu($limit['datemin'],$limit['datemax']);
		//so kh
		$this->data['khtc'] = $this->getKH($limit['datemin'],$limit['datemax']);
		//kh moi
		$this->data['khmoi'] = $this->data['khtc'] - $this->data['khcu'];
		$arr_in = $this->getTranNotCompleted();
		//phieu cu
		$sql = "select count(ID) as total from phieukh where Tran_ID not in $arr_in and gia=0 and ((sosuat=5 and conlai < 5) or (sosuat=10 and conlai < 12)) and Create_Dt >= ".$limit['datemin']." AND Create_Dt <= ".$limit['datemax']." and Active = 1 and Saved = 1";
		$total = $this->CodeGenerator_model->custom_query($sql,"array");
		$this->data['phieucu'] = $total[0]['total'];
		//phieu moi
		$sql = "select count(ID) as total from phieukh where Tran_ID not in $arr_in and gia >0 and ((sosuat=5 and conlai = 5) or (sosuat=10 and conlai = 12)) and Create_Dt >= ".$limit['datemin']." AND Create_Dt <= ".$limit['datemax']." and Active = 1 and Saved = 1";
		$total = $this->CodeGenerator_model->custom_query($sql,"array");
		$this->data['phieumoi'] = $total[0]['total'];
		//tien mat
		$sql = "select sum(Price) as total from transaction where Completed = 2 and Create_Dt >= ".$limit['datemin']." AND Create_Dt <= ".$limit['datemax'];
		$total = $this->CodeGenerator_model->custom_query($sql,"array");
		$this->data['tienmat'] = $total[0]['total'];
		//tien tips
		$this->data['tientip'] = $this->getTienTip($limit['datemin'],$limit['datemax']);
		//% service
		$this->data['percent_service'] = $this->getPercentService($limit['datemin'],$limit['datemax']);
		//tong
		$this->data['tongket'] = $this->data['tienmat'] - $this->data['tientip'] - $this->data['percent_service'];
        
        $this->data[ 'results'        ]     =   $results;
        $this->data[ 'master_limited' ]     =   explode(',', 'muc,dichvu,kh,giatridichvu,percent_service,tips,conlai'); 
		
		// $this->load->view( 'employee_list', $this->data ); 
		$this->template->write( 'title', 'Spa - Tong ket thu' );
		// $this->template->add_css( 'resources/css/users.css' );
        $this->template->write_view( 'content', 'tongket.php',$this->data );
        $this->template->render();
    } 

	function thuchi(){
		$loai = 1; //tháng
		$time=$this->input->post('thuchi_dt');	

		$selected_cat=$this->input->post('theo');
		if(empty($selected_cat)) $selected_cat = 'M';
		if(empty($time))
			$time = date("m/Y",time());
		$dt = explode("/",$time);
		if(count($dt)==2){
			$this->data['thang'] = $dt[0];
			$this->data['time'] = $time;
		}else {$this->data['thang'] = 1;
		$this->data['time'] = "";
		}
		
		
		$combobox_cats['M']="Thống kê theo tháng";
		$combobox_cats['CM']="Thống kê theo mốc";
		//$combobox_cats['Y']="Thống kê theo năm";
		$time_from=$this->input->post('thuchi_dt_from');
		$time_to=$this->input->post('thuchi_dt_to');
		if(empty($time_from))
			$time_from = date("d/m/Y",time());
		if(empty($time_to))
			$time_to = date("d/m/Y",time());
		$this->data['time_from'] = $time_from;
		$this->data['time_to'] = $time_to;
		$this->data['combobox_cats']=form_dropdown('theo', $combobox_cats,$selected_cat);
		if($selected_cat == "CM"){
			$limit['datemin']= $this->convertDatetoTimeStamp($time_from,1);
			$limit['datemax']= $this->convertDatetoTimeStamp($time_to,0);
		}else
			$limit = $this->getLimit($selected_cat,"06/".$time);
		//thong tin luong
		$results =  $this->getResultsDichVu($limit['datemin'],$limit['datemax']);
		$this->data['luong'] = $results['rows'];
		
		//chi phi phat sinh
		$sql = "select * from phatsinh where Create_Dt >= ".$limit['datemin']." AND Create_Dt <= ".$limit['datemax'] ;

		$results = $this->CodeGenerator_model->custom_query($sql,"array");
		$this->data['phatsinh'] = $results;
		
		//thu
		$sql="select sum(money) as total from transaction where Create_Dt >= ".$limit['datemin']." AND Create_Dt <= ".$limit['datemax'] ;
		$total = $this->CodeGenerator_model->custom_query($sql,"array");
		$thanhtien = 0; 
		if(!empty($total)){
			$thanhtien = $total[0]['total'];
		}
		$this->data['thu'] = $thanhtien;
		
		//tien tip
		$this->data['tientip'] = $this->getTientip($limit['datemin'],$limit['datemax']);
		
		$this->template->write( 'title', 'Spa - Thu chi' );
        $this->template->write_view( 'content', 'thuchi.php',$this->data );
        $this->template->render();
		/*
		$body = '';
		ob_start ();
		$this->load->view ( 'transaction_add', $this->data );
		$body = ob_get_clean ();
		echo $body;
		exit();
		*/
	}
}

/* End of file index.php */
/* Location: ./index.php */